![BigQueryで測定系の地理関数を確認してみた(BigQuery GIS)](https://devio2023-media.developers.io/wp-content/uploads/2020/09/gcp-eyecatch-bigquery_1200x630.png)
BigQueryで測定系の地理関数を確認してみた(BigQuery GIS)
当エントリは、『クラスメソッド BigQuery Advent Calendar 2020』15日目のエントリです。 本アドベントカレンダーでは、12月01日〜12月25日までの25日間、弊社DA(データアナリィクス)事業本部のメンバーがBigQueryに関連するブログを公開していきます。
BigQueryはDWHとしての機能だけでなく、BigQuery GIS(地理情報システム)を用いて地理データ型と標準のSQL地理関数を使用して、地理空間データを分析および可視化を行うことが出来ます。 当エントリでは、標準のSQL地理関数の一部(測定系のみ)を紹介をしていきます。
測定系の地理関数
現時点(2020/12/15)で利用できる測定系の地理関数は5つあり、それぞれ簡単にまとめます。
ST_AREA
ST_AREA(geography_expression[, use_spheroid])
- 戻り値(FLOAT64):GEOGRAPHYの面積(平方メートル)
- pointやlineが入力された場合は0を返却
- collectionが入力された場合はpolygonの面積の合計を返却
- use_spheroid=FALSEの場合は完全な球体の表面上の距離として計算される、現時点でFALSEのみサポート
使用例
select ST_AREA(ST_GeogFromText('POLYGON((-179 26, -179 48, -10 48, -10 26, -100 -10.1, -179 26))')) as area +-----------------------+ | area | +-----------------------+ | 9.564128913265508E13 | +-----------------------+
使用例では、下記を施しています。
- WKT(Well-known text)データである
'POLYGON((-179 26, -179 48, -10 48, -10 26, -100 -10.1, -179 26))'
をST_GeogFromText
関数でGEOGRAPHY型データに変換 - 変換されたGEOGRAPHY型データの面積を
ST_AREA
関数で算出
ST_DISTANCE
ST_DISTANCE(geography_1, geography_2[, use_spheroid])
- 戻り値(FLOAT64):二つのGEOGRAPHY間の最短距離(m)
- pointやlineが入力された場合は0を返却
- use_spheroid=FALSEの場合は完全な球体の表面上の距離として計算される、現時点でFALSEのみサポート
使用例
select ST_DISTANCE(ST_GEOGPOINT(139.7454316, 35.658584),ST_GEOGPOINT(139.8108103, 35.7100069)) as distance +-------------------+ | distance | +-------------------+ | 8219.632821167155 | +-------------------+
使用例では、下記を施しています。
- それぞれの緯度(latitude),経度(longitude)を
ST_GEOGPOINT
関数でGEOGRAPHY型データに変換 - 変換されたそれぞれのGEOGRAPHY型データの最短距離を
ST_DISTANCE
関数で算出
ST_LENGTH
ST_LENGTH(geography_expression[, use_spheroid])
- 戻り値(FLOAT64):入力されたGEOGRAPHYに含まれるラインの長さの合計(m)
- GEOGRAPHYのどちらか片方がemptyの場合は、NULLを返却
- use_spheroid=FALSEの場合は完全な球体の表面上の距離として計算される、現時点でFALSEのみサポート
使用例
select ST_LENGTH(ST_MAKELINE(ST_GEOGPOINT(139.7454316, 35.658584),ST_GEOGPOINT(139.8108103, 35.7100069))) as length +-------------------+ | length | +-------------------+ | 8219.632821166997 | +-------------------+
使用例では、下記を施しています。
- それぞれの緯度(latitude),経度(longitude)を
ST_GEOGPOINT
関数でGEOGRAPHY型データに変換 - 変換されたそれぞれのGEOGRAPHY型データから
ST_MAKELINE
関数でライン(GEOGRAPHY型)を生成 - 生成されたライン(GEOGRAPHY型)の合計の長さを
ST_LENGTH
関数で算出
ST_MAXDISTANCE
ST_MAXDISTANCE(geography_1, geography_2[, use_spheroid])
- 戻り値(FLOAT64):距離が最大となる二点間の距離(m)
- GEOGRAPHYのどちらか片方がemptyの場合は、NULLを返却
- use_spheroid=FALSEの場合は完全な球体の表面上の距離として計算される、現時点でFALSEのみサポート
使用例
select ST_MAXDISTANCE(ST_GEOGPOINT(139.7454316, 35.658584),ST_GEOGPOINT(139.8108103, 35.7100069)) as max_distance +-------------------+ | max_distance | +-------------------+ | 8219.632821167155 | +-------------------+
使用例では、下記を施しています。
- それぞれの緯度(latitude),経度(longitude)を
ST_GEOGPOINT
関数でGEOGRAPHY型データに変換 - 変換されたそれぞれのGEOGRAPHY型データから
ST_MAXDISTANCE
関数で二点間の最大距離を算出
ST_PERIMETER
ST_PERIMETER(geography_expression[, use_spheroid])
- 戻り値(FLOAT64):入力されたGEOGRAPHYの外周の長さの合計(m)
- pointやlineが入力された場合は0を返却
- collectionが入力された場合はpolygonの面積の合計を返却
- use_spheroid=FALSEの場合は完全な球体の表面上の距離として計算される、現時点でFALSEのみサポート
使用例
select ST_PERIMETER(ST_GeogFromText('POLYGON((-179 26, -179 48, -10 48, -10 26, -100 -10.1, -179 26))')) as perimeter +---------------------+ | perimeter | +---------------------+ | 3.409889315220319E7 | +---------------------+
使用例では、下記を施しています。
- WKT(Well-known text)データである
'POLYGON((-179 26, -179 48, -10 48, -10 26, -100 -10.1, -179 26))'
をST_GeogFromText
関数でGEOGRAPHY型データに変換 - 変換されたGEOGRAPHY型データから
ST_PERIMETER
関数で外周の長さの合計を算出
おまけ:ST_DISTANCE関数でデータを絞ってみた
上記の関数紹介を踏まえて、実際に一般公開データセットからST_DISTANCE関数を使ってデータを絞ってみました。
select station_id, name from `bigquery-public-data.new_york.citibike_stations` where ST_DISTANCE(ST_GEOGPOINT(longitude, latitude), ST_GEOGPOINT(-73.99404649, 40.72903917)) >= 10000
Washington Pl & Broadway(緯度:40.72903917、経度:-73.99404649)という駅との距離が10km以上離れている自転車ステーションを抽出してみました。
おわりに
今回は測定系の地理関数に絞って、簡単に確認してみました。 基本的に他のGIS機能(PostGISなど)と同等の関数が使えるようですね、あまりGIS自体を触ってなかった自分にとっては新鮮でした。
地理関数は他のカテゴリもあり、いろんな空間分析する際に利用できそうなので引き続き確認していこうと思います。 また今回は試せなかったのですが、実際は地理関数などで抽出したデータをBigQuery Geo Viz可視化するまでが一般的な使い方だと思うので、BigQuery Geo Vizについても確認していこうと思います。 BigQuery Geo Vizは下記のブログなどでも紹介されているので、あわせてご確認ください。
『クラスメソッド BigQuery Advent Calendar 2020』 16日目は、みかみさんです。お楽しみに! 以上、DA(データアナリティクス)事業本部のナガマサでした。